Skip to content

9.16 Sql 操作

📝 模块更新日志 问题修复*

+ 修复 `SQL` 查询结果转模型不支持 `DateOnly` 和 `TimeOnly` 属性类型 4\.9\.2\.7 ⏱️2024\.04\.04 [31f9d23](https://gitee.com/dotnetchina/Furion/commit/31f9d23c430b531c78e848ab62476f3fbac73dfd)
+ 修复 使用达梦数据库执行 `sql` 不能自动修复命令参数前缀 4\.8\.7\.18 ⏱️2023\.03\.21 [\#I6OK4T](https://gitee.com/dotnetchina/Furion/issues/I6OK4T)

温馨提示推荐使用 《9.18 Sql 高级代理》代替本章节功能。Sql 高级代理 能够提供更容易且更易维护的方式。

例子说明本章节例子均以 SqlServer 数据库写的例子，命令参数统一用 @ 符号，但不同数据库的参数前缀有所不同，如：sql server 采用 @，Oracle/DM 采用 :，my sql 采用 ?。

9.16.1 关于 Sql​

Furion 框架提供非常多且灵活的 sql 操作方法，且性能不输于 dapper，同时接近 ADO.NET 原生操作。

9.16.2 懒人无敌 Sql 🐮​

9.16.2.1 返回 DataTable​

// ==== 同步操作 ====  

// 示例一  
var dataTable = "select * from person".SqlQuery();  

// 示例二  
var dataTable = "select top 10 * from person where id > @id".SqlQuery(new {id = 10});  

// 示例三  
var dataTable = "select Id, Name, Age from person where name like @name".SqlQuery(new Dictionary<string,object>{ {"name", "%Furion%"} });  

// 示例四  
var dataTable = "select * from person where name=@name limit 1,10".SqlQuery(new []{ new MySqlParameter("name","Furion") });  

// 示例五  
var dataTable = "select * from person where id>@id and name like @name".SqlQuery(new YourModel { Id = 1, Name = "%Furion%" });  

// 示例六  
var dataTable = "exec PROC_GetPerson @id".SqlQuery(new {id = 10});  

// 示例七  
var dataTable = "select * from FN_GetPersons(@id)".SqlQuery(new {id = 10});  

// 示例八  
var dataTable = @"  
select * from person p  
left join personDetail pd on p.Id == pd.pid  
where p.Id > @id;".SqlQuery(new {id = 10});  

// ==== 异步操作 ====  

// 示例一  
var dataTable = await "select * from person".SqlQueryAsync();  

// 示例二  
var dataTable = await "select top 10 * from person where id > @id".SqlQueryAsync(new {id = 10});  

// 示例三  
var dataTable = await "select Id, Name, Age from person where name like @name".SqlQueryAsync(new Dictionary<string,object>{ {"name", "%Furion%"} });  

// 示例四  
var dataTable = await "select * from person where name=@name limit 1,10".SqlQueryAsync(new []{ new MySqlParameter("name","Furion") });  

// 示例五  
var dataTable = await "select * from person where id>@id and name like @name".SqlQueryAsync(new YourModel { Id = 1, Name = "%Furion%" });  

// 示例六  
var dataTable = await "exec PROC_GetPerson @id".SqlQueryAsync(new {id = 10});  

// 示例七  
var dataTable = await "select * from FN_GetPersons(@id)".SqlQueryAsync(new {id = 10});  

// 示例八  
var dataTable = await @"  
select * from person p  
left join personDetail pd on p.Id == pd.pid  
where p.Id > @id;".SqlQueryAsync(new {id = 10});

9.16.2.2 返回 List<T>​

// ==== 同步操作 ====  

// 示例一  
var persons = "select * from person".SqlQuery<Person>();  

// 示例二  
var persons = "select top 10 * from person where id > @id".SqlQuery<Person>(new {id = 10});  

// 示例三  
var persons = "select Id, Name, Age from person where name like @name".SqlQuery<Person>(new Dictionary<string,object>{ {"name", "%Furion%"} });  

// 示例四  
var persons = "select * from person where name=@name limit 1,10".SqlQuery<Person>(new []{ new MySqlParameter("name","Furion") });  

// 示例五  
var persons = "select * from person where id>@id and name like @name".SqlQuery<Person>(new YourModel { Id = 1, Name = "%Furion%" });  

// 示例六  
var persons = "exec PROC_GetPerson @id".SqlQuery<Person>(new {id = 10});  

// 示例七  
var persons = "select * from FN_GetPersons(@id)".SqlQuery<Person>(new {id = 10});  

// 示例八  
var persons = @"  
select * from person p  
left join personDetail pd on p.Id == pd.pid  
where p.Id > @id;".SqlQuery<Person>(new {id = 10});  

// ==== 异步操作 ====  

// 示例一  
var persons = await "select * from person".SqlQueryAsync<Person>();  

// 示例二  
var persons = await "select top 10 * from person where id > @id".SqlQueryAsync<Person>(new {id = 10});  

// 示例三  
var persons = await "select Id, Name, Age from person where name like @name".SqlQueryAsync<Person>(new Dictionary<string,object>{ {"name", "%Furion%"} });  

// 示例四  
var persons = await "select * from person where name=@name limit 1,10".SqlQueryAsync<Person>(new []{ new MySqlParameter("name","Furion") });  

// 示例五  
var persons = await "select * from person where id>@id and name like @name".SqlQueryAsync<Person>(new YourModel { Id = 1, Name = "%Furion%" });  

// 示例六  
var persons = await "exec PROC_GetPerson @id".SqlQueryAsync<Person>(new {id = 10});  

// 示例七  
var persons = await "select * from FN_GetPersons(@id)".SqlQueryAsync<Person>(new {id = 10});  

// 示例八  
var persons = await @"  
select * from person p  
left join personDetail pd on p.Id == pd.pid  
where p.Id > @id;".SqlQueryAsync<Person>(new {id = 10});

9.16.2.3 返回 DataSet​

// ==== 同步操作 ====  

// 示例一  
var dataSet = @"  
select * from person;  
select * from student;".SqlQueries();  

// 示例二  
var dataSet = @"  
select * from person where Id > @id;  
select * from student where Name like @name;".SqlQueries(new {id = 1, name = "%furion%"});  

// 示例三  
var dataSet = @"  
select * from person;  
exec PROC_GetStudents(@id);  
select 'Furion';  
select * from FN_GetPerson(@id);".SqlQueries(new {id = 1});  

// ==== 异步操作 ====  

// 示例一  
var dataSet = await @"  
select * from person;  
select * from student;".SqlQueriesAsync();  

// 示例二  
var dataSet = await @"  
select * from person where Id > @id;  
select * from student where Name like @name;".SqlQueriesAsync(new {id = 1, name = "%furion%"});  

// 示例三  
var dataSet = await @"  
select * from person;  
exec PROC_GetStudents(@id);  
select 'Furion';  
select * from FN_GetPerson(@id);".SqlQueriesAsync(new {id = 1});

9.16.2.4 返回 Tuple<T1,...T8>​

// ==== 同步操作 ====  

// 示例一  
var (persons, students) = @"  
select * from person;  
select * from student;".SqlQueries<Person,Student>();  

// 示例二  
var (persons, students) = @"  
select * from person where Id > @id;  
select * from student where Name like @name;".SqlQueries<Person,Student>(new {id = 1, name = "%furion%"});  

// 示例三  
var (persons, students, string, PersonDto) = @"  
select * from person;  
exec PROC_GetStudents(@id);  
select 'Furion';  
select * from FN_GetPerson(@id);".SqlQueries<Person, Student, string, PersonDto>(new {id = 1});  

// ==== 异步操作 ====  

// 示例一  
var (persons, students) = await @"  
select * from person;  
select * from student;".SqlQueriesAsync<Person,Student>();  

// 示例二  
var (persons, students) = await @"  
select * from person where Id > @id;  
select * from student where Name like @name;".SqlQueriesAsync<Person,Student>(new {id = 1, name = "%furion%"});  

// 示例三  
var (persons, students, string, PersonDto) = await @"  
select * from person;  
exec PROC_GetStudents(@id);  
select 'Furion';  
select * from FN_GetPerson(@id);".SqlQueriesAsync<Person, Student, string, PersonDto>(new {id = 1});

9.16.2.5 返回 单行单列​

// ==== 同步操作 ====  

// 示例一  
var value = "select Name from person where id = @id".SqlScalar(new {id = 1});  

// 示例二  
var value = "select Name from person where id = @id".SqlScalar<string>(new {id = 1});  

// 示例三  
var value = "select Age from person where id = @id".SqlScalar<int>(new {id = 1});  

// ==== 异步操作 ====  

// 示例一  
var value = await "select Name from person where id = @id".SqlScalarAsync(new {id = 1});  

// 示例二  
var value = await "select Name from person where id = @id".SqlScalarAsync<string>(new {id = 1});  

// 示例三  
var value = await "select Age from person where id = @id".SqlScalarAsync<int>(new {id = 1});

9.16.2.6 返回 受影响行数​

// ==== 同步操作 ====  

// 示例一  
var rowEffects = "insert into person(Name,Age,Address) values(@name,@age,@address)".SqlNonQuery(person);  

// 示例二  
var rowEffects = @"  
insert into person(Name,Age,Address) values(@name,@age,@address);  
insert into person(Name,Age,Address) values(@name,@age,@address);".SqlNonQuery(persons);  

// 示例三  
var rowEffects = "update person set name=@name where id=@id".SqlNonQuery(new {id=1, name="百小僧"});  

// 示例四  
var rowEffects = "delete from person where @id > 10".SqlNonQuery(new {id=1});  

// ==== 异步操作 ====  

// 示例一  
var rowEffects = await "insert into person(Name,Age,Address) values(@name,@age,@address)".SqlNonQueryAsync(person);  

// 示例二  
var rowEffects = @"  
insert into person(Name,Age,Address) values(@name,@age,@address);  
insert into person(Name,Age,Address) values(@name,@age,@address);".SqlNonQueryAsync(persons);  

// 示例三  
var rowEffects = await "update person set name=@name where id=@id".SqlNonQueryAsync(new {id=1, name="百小僧"});  

// 示例四  
var rowEffects = await "delete from person where @id > 10".SqlNonQueryAsync(new {id=1});

9.16.3 懒人无敌 存储过程 🐮​

9.16.3.1 返回 DataTable​

// ==== 同步操作 ====  

// 示例一  
var dataTable = "PROC_Name".SqlProcedureQuery();  

// 示例二  
var dataTable = "PROC_Name".SqlProcedureQuery(new {id = 1});  

// 示例三  
var dataTable = "PROC_Name".SqlProcedureQuery(new {id = 1, age = 27});  

// ==== 异步操作 ====  

// 示例一  
var dataTable = await "PROC_Name".SqlProcedureQueryAsync();  

// 示例二  
var dataTable = await "PROC_Name".SqlProcedureQueryAsync(new {id = 1});  

// 示例三  
var dataTable = await "PROC_Name".SqlProcedureQueryAsync(new {id = 1, age = 27});

9.16.3.2 返回 List<T>​

// ==== 同步操作 ====  

// 示例一  
var persons = "PROC_Name".SqlProcedureQuery<Person>();  

// 示例二  
var persons = "PROC_Name".SqlProcedureQuery<Person>(new {id = 1});  

// 示例三  
var persons = "PROC_Name".SqlProcedureQuery<Person>(new {id = 1, age = 27});  

// ==== 异步操作 ====  

// 示例一  
var persons = await "PROC_Name".SqlProcedureQueryAsync<Person>();  

// 示例二  
var persons = await "PROC_Name".SqlProcedureQueryAsync<Person>(new {id = 1});  

// 示例三  
var persons = await "PROC_Name".SqlProcedureQueryAsync<Person>(new {id = 1, age = 27});

9.16.3.3 返回 DataSet​

// ==== 同步操作 ====  

// 示例一  
var dataSet = "PROC_Name".SqlProcedureQueries();  

// 示例二  
var dataSet = "PROC_Name".SqlProcedureQueries(new {id = 1});  

// 示例三  
var dataSet = "PROC_Name".SqlProcedureQueries(new {id = 1, age = 27});  

// ==== 异步操作 ====  

// 示例一  
var dataSet = await "PROC_Name".SqlProcedureQueriesAsync();  

// 示例二  
var dataSet = await "PROC_Name".SqlProcedureQueriesAsync(new {id = 1});  

// 示例三  
var dataSet = await "PROC_Name".SqlProcedureQueriesAsync(new {id = 1, age = 27});

9.16.3.4 返回 Tuple<T1,...T8>​

// ==== 同步操作 ====  

// 示例一  
var persons = "PROC_Name".SqlProcedureQueries<Person>();  

// 示例二  
var (persons,students) = "PROC_Name".SqlProcedureQueries<Person,Student>(new {id = 1});  

// 示例三  
var (persons,students,string) = "PROC_Name".SqlProcedureQueries<Person,Student,string>(new {id = 1, age = 27});  

// 示例四  
var (persons,students,personDetail,string) = "PROC_Name".SqlProcedureQueries<Person,Student,PersonDetail,string>(new {id = 1, age = 27});  

// ==== 异步操作 ====  

// 示例一  
var persons = await "PROC_Name".SqlProcedureQueriesAsync<Person>();  

// 示例二  
var (persons,students) = await "PROC_Name".SqlProcedureQueriesAsync<Person,Student>(new {id = 1});  

// 示例三  
var (persons,students,string) = await "PROC_Name".SqlProcedureQueriesAsync<Person,Student,string>(new {id = 1, age = 27});  

// 示例四  
var (persons,students,personDetail,string) = await "PROC_Name".SqlProcedureQueriesAsync<Person,Student,PersonDetail,string>(new {id = 1, age = 27});

9.16.3.5 返回 单行单列​

// ==== 同步操作 ====  

// 示例一  
var value = "PROC_Name".SqlProcedureScalar(new {id = 1});  

// 示例二  
var value = "PROC_Name".SqlProcedureScalar<string>(new {id = 1, name = "新生帝", address ="广东省中山市"});  

// 示例三  
var value = "PROC_Name".SqlProcedureScalar<int>(new {id = 1,  address ="广东省中山市"});  

// ==== 异步操作 ====  

// 示例一  
var value = await "PROC_Name".SqlProcedureScalarAsync(new {id = 1});  

// 示例二  
var value = await "PROC_Name".SqlProcedureScalarAsync<string>(new {id = 1, name = "新生帝", address ="广东省中山市"});  

// 示例三  
var value = await "PROC_Name".SqlProcedureScalarAsync<int>(new {id = 1,  address ="广东省中山市"});

9.16.3.6 返回 受影响行数​

// ==== 同步操作 ====  

// 示例一  
var rowEffects = "PROC_Name".SqlProcedureNonQuery(person);  

// 示例二  
var rowEffects = "PROC_Name".SqlProcedureNonQuery(new {id = 1, name = "新生帝", address ="广东省中山市"});  

// 示例三  
var rowEffects = "PROC_Name".SqlProcedureNonQuery(new {id=1, name="百小僧"});  

// 示例四  
var rowEffects = "PROC_Name".SqlProcedureNonQuery(new {id=1});  

// ==== 异步操作 ====  

// 示例一  
var rowEffects = await "PROC_Name".SqlProcedureNonQueryAsync(person);  

// 示例二  
var rowEffects = await "PROC_Name".SqlProcedureNonQueryAsync(new {id = 1, name = "新生帝", address ="广东省中山市"});  

// 示例三  
var rowEffects = await "PROC_Name".SqlProcedureNonQueryAsync(new {id=1, name="百小僧"});  

// 示例四  
var rowEffects = await "PROC_Name".SqlProcedureNonQueryAsync(new {id=1});

9.16.3.7 带 OUTPUT/RETURN 返回​

CREATE PROC PROC_Output  
    @Id INT,    // 输入参数  
    @Name NVARCHAR(32) OUTPUT,  // 输出参数，还带长度  
    @Age INT OUTPUT // 输出参数  
AS  
BEGIN  
    SET @Name = 'Furion Output';  

    // 输出结果集  
    SELECT *  
    FROM dbo.Test  
    WHERE Id > @Id;  

    // 输出结果集  
    SELECT TOP 10  
           *  
    FROM dbo.Test;  

    SET @Age = 27;  

    // 带 RETURN 返回  
    RETURN 10;  
END;  


using Furion.DatabaseAccessor;  
using System.Data;  

namespace Furion.Application  
{  
    public class ProcOutputModel  
    {  
        public int Id { get; set; } // 输入参数  

        [DbParameter(ParameterDirection.Output, Size = 32)]  
        public string Name { get; set; }    // 输出参数  

        [DbParameter(ParameterDirection.Output)]  
        public int Age { get; set; }    // 输出参数  

        [DbParameter(ParameterDirection.ReturnValue)]  
        public int ReturnValue { get; set; }    // 返回值  
    }  
}  


// ==== 同步操作 ====  

// 示例一  
ProcedureOutputResult result = "PROC_Name".SqlProcedureOutput(new ProcOutputModel{ Id=1});  

// 示例二  
ProcedureOutputResult result = "PROC_Name".SqlProcedureOutput(new ProcOutputModel{ Id=1});  

// 示例三  
ProcedureOutputResult<(List<Person>, List<Student>)> result = "PROC_Name".SqlProcedureOutput<(List<Person>, List<Student>)>(new ProcOutputModel{ Id=1});  

// ==== 异步操作 ====  
// 示例一  
ProcedureOutputResult result = await "PROC_Name".SqlProcedureOutputAsync(new ProcOutputModel{ Id=1});  

// 示例二  
ProcedureOutputResult result = await "PROC_Name".SqlProcedureOutputAsync(new ProcOutputModel{ Id=1});  

// 示例三  
ProcedureOutputResult<(List<Person>, List<Student>)> result = await "PROC_Name".SqlProcedureOutputAsync<(List<Person>, List<Student>)>(new ProcOutputModel{ Id=1});

9.16.4 懒人无敌 函数 🐮​

9.16.4.1 标量函数​

// ==== 同步操作 ====  

// 示例一  
var value = "FN_Name".SqlFunctionScalar();  

// 示例二  
var value = "FN_Name".SqlFunctionScalar(new {id = 1});  

// 示例三  
var value = "FN_Name".SqlFunctionScalar<string>();  

// 示例四  
var value = "FN_Name".SqlFunctionScalar<int>(new {id = 1});  

// ==== 异步操作 ====  

// 示例一  
var value = await "FN_Name".SqlFunctionScalarAsync();  

// 示例二  
var value = await "FN_Name".SqlFunctionScalarAsync(new {id = 1});  

// 示例三  
var value = await "FN_Name".SqlFunctionScalarAsync<string>();  

// 示例四  
var value = await "FN_Name".SqlFunctionScalarAsync<int>(new {id = 1});

9.16.4.2 表值函数​

// ==== 同步操作 ====  

// 示例一  
var dataTable = "FN_Name".SqlFunctionQuery();  

// 示例二  
var dataTable = "FN_Name".SqlFunctionQuery(new {id = 1});  

// 示例三  
var persons = "FN_Name".SqlFunctionQuery<Person>();  

// 示例四  
var persons = "FN_Name".SqlFunctionQuery<Person>(new {id = 1});  

// ==== 异步操作 ====  

// 示例一  
var dataTable = await "FN_Name".SqlFunctionQueryAsync();  

// 示例二  
var dataTable = await "FN_Name".SqlFunctionQueryAsync(new {id = 1});  

// 示例三  
var persons = await "FN_Name".SqlFunctionQueryAsync<Person>();  

// 示例四  
var persons = await "FN_Name".SqlFunctionQueryAsync<Person>(new {id = 1});

9.16.5 设置超时时间​

var data = "select * from table".SetCommandTimeout(100).SqlQuery(); // 单位秒

9.16.6 ISqlRepository 操作​

ISqlRepository 仓储是专门处理 Sql 操作的，无需实体方式，所有接口和 懒人无敌 方式一样：

// 示例一  
var dataTable = sqlRepository.SqlQuery("select * from person");  

// 示例二  
var dataTable = sqlRepository.SqlQuery("select * from person where id > @id", new { id = 10});  

// 示例四  
var persons = sqlRepository.SqlQuery<Person>("select * from person");  

// 示例五  
var persons = sqlRepository.SqlQuery<Person>("select * from person where id > @id", new { id = 10});  

// 不再举例子。。。

补充说明不管是哪种方式操作 Sql ，方法名参数都是一致的，如：

  • SqlQuery
  • SqlQueryAsync
  • SqlQueries
  • SqlQueriesAsync
  • SqlNonQuery
  • SqlNonQueryAsync
  • SqlScalar
  • SqlScalarAsync
  • SqlProcedureQuery
  • SqlProcedureQueryAsync
  • SqlProcedureQueries
  • SqlProcedureQueriesAsync
  • SqlProcedureScalar
  • SqlProcedureScalarAsync
  • SqlProcedureNonQuery
  • SqlProcedureNonQueryAsync
  • SqlProcedureOutput
  • SqlProcedureOutputAsync
  • SqlFunctionScalar
  • SqlFunctionScalarAsync
  • SqlFunctionQuery
  • SqlFunctionQuery

9.16.7 IRepository 操作​

IRepository 也能操作 sql，调用方法也是和上面一致的，如：

var dataTable = repository.Sql().SqlQuery("select * from person");

特别说明由于篇幅有限，不再列举所有例子。

9.16.8 IRepository<TEntity> 操作​

IRepository<TEntity> 也能操作 sql，调用方法也是和上面一致的，如：

var dataTable = personRepository.SqlQuery("select * from person");

特别说明由于篇幅有限，不再列举所有例子。

9.16.9 关于 Sql 参数​

所有 sql、存储过程，函数 参数都支持四种方式：

  • DbParameter[]：数组类型
  • new {}：匿名类型
  • new Class{}：强类型类型（支持复杂存储过程参数）
  • Dictionary<string,object> 类型

小知识建议除了复杂的存储过程（带 OUTPUT/RETURN）的以外，所有参数建议使用 new {} 匿名类型，如果需要动态参数，则可以使用 Dictionary<string,object> 类型。

参数大小写问题由于不同数据库对查询参数大小写问题处理不一致，所以建议所有查询参数和参数名或属性名完全一致。

9.16.10 多数据库 Sql 操作 💯 💛​

Furion 框架拥有非常灵活的多数据库操作方式，只需通过多数据库上下文定位器即可动态切换数据库。

9.16.10.1 懒人无敌 🐮 方式​

var dataTable = "select * from person".Change<MySqlDbContextLocator>().SqlQuery();  

var persons = "select * from person whre id > @id".Change<SqliteDbContextLocator>().SqlQuery<Person>();

补充说明懒人方式 只需要通过 Change<TDbContextLocator> 方式即可动态切换数据库。

9.16.10.2 ISqlRepository 方式​

只需要通过 ISqlRepository<TDbContextLocator> 注入或通过 sqlRepository.Change<TDbContextLocator>() 切换。

9.16.10.3 IRepository 方式​

只需要通过 repository.Change<TDbContextLocator>() 获取即可。

9.16.10.4 IRepository<TEntity> 方式​

只需要通过 IRepository<TEntity, TDbContextLocator> 注入或通过 personRepository.Change<TEntity, TDbContextLocator>() 切换。

9.16.11 切换数据库​

在 Furion 框架中，不管是懒人模式还是仓储模式都是通过 .Change<TDbContextLocator> 方式切换数据库，如：

// 懒人模式  
var data = "select * from table".Change<MySqlDbContextLocator>().SqlQuery<Data>();  

// 仓储方式  
var data = req.Change<MySqlDbContextLocator>().SqlQuery<Data>("select * from table");

9.16.12 多线程共享作用域​

默认情况下，所有的 字符串 和 实体 拓展都有自己独立维护的 ServiceProvider 作用域。

在 Web 请求中，默认是 HttpContext.RequestServices，但在 非 Web，如多线程操作，后台任务，事件总线等场景下会自动创建新的作用域，实际上这是非常不必要的内存开销。

这时，我们只需要通过 .SetXXXScoped(service) 共享当前服务提供器作用域即可，如：

Scoped.Create((fac, scope) => {  
    "select * from table".SetContextScoped(scope.ServiceProvider).SqlQuery();  
});

9.16.13 静态 Default 方式构建​

SqlExecutePart.Default.SetSqlString("select * from person").SqlQuery();

9.16.14 反馈与建议​

与我们交流给 Furion 提 Issue。